package CategoriesLocalDatabase;

use strict;
use warnings;
use DBI;
use IMBK::Data::Source::Danbooru;

sub new {
	my $class = shift;
	my $self = { @_ };
	
	my $db_path = $self->{Settings_Dir}.'/'.$self->{Categories_Database_Filename};
	
	if ( not -e $db_path ) {
		$self->{Database} = create_database($self) or die "Cannot create database";
	} else {
		$self->{Database} = DBI->connect($self->{Categories_Database_Driver}.':dbname='.$db_path,"","") or die;
		$self->{Database}->do('PRAGMA foreign_keys = ON;');
		check_database($self);
	}
	return bless $self, $class;
};

sub create_database {
	my $self = shift;
	my $driver = $self->{Categories_Database_Driver};
	my $database_path = $self->{Settings_Dir}.'/'.$self->{Categories_Database_Filename};
	my @create_order = ( 'Categories', 'Keywords' );
	my %sql_create_tables = ( Keywords => <<"EOT"
	CREATE TABLE keywords ( 
		name NVARCHAR CONSTRAINT pk_Key PRIMARY KEY,
		category_id INTEGER CONSTRAINT fk_CatKey REFERENCES categories(category_id),
		blacklisted char(1) DEFAULT F NOT NULL 
	);
EOT
	, Categories => <<"EOT"
	CREATE TABLE categories (
		category_id INTEGER CONSTRAINT pk_Cat PRIMARY KEY AUTOINCREMENT,
		category VARCHAR NOT NULL
	);
EOT
	);
	my $sql_insert_categories = "INSERT INTO categories ( category ) VALUES ( ? )";
	my @categories = ( 'General', 'Artist', 'Copyrights', 'Characters' );
	
	my $dbh = DBI->connect($driver.':dbname='.$database_path,"","") or return undef;
	$dbh->do('PRAGMA foreign_keys = ON;');
	$dbh->do('BEGIN');
	my $sth = undef;
	foreach ( @create_order ) {
		$sth = $dbh->do($sql_create_tables{$_});
	}
	
	$sth = $dbh->prepare($sql_insert_categories);
	foreach ( @categories ) {
		$sth->execute($_);
	}
	$dbh->do('COMMIT');
	
	return $dbh;
};

sub check_database {
	my $self = shift;
	my $dbh = $self->{Database};
};

sub get_categories {
	my $self = shift;
	my $args =  join ',', map { qq/'$_'/ } @_;
	my %result = map { $_ => undef } @_;
	my %converter = ( 'category-4' => 'Characters', 'category-3' => 'Copyrights', 'category-1' => 'Artist', 'category-0' => 'General' );
	my $sql_query = <<"EOT";
	SELECT keywords.name, categories.category FROM keywords JOIN categories
	ON keywords.category_id = categories.category_id 
	WHERE keywords.name IN ($args)
EOT
	my $categories_source = Danbooru->new();
	my $dbh = $self->{Database};
	my $sth = $dbh->prepare($sql_query);
	$sth->execute();
	
	while ( my ( $name, $category ) = $sth->fetchrow_array() ) {
		$result{$name} = $category;
	}
	
	my @keys_without_category = grep { not defined $result{$_} } keys %result;
	my %new_keywords = $categories_source->get_categories(@keys_without_category);
	my %category_id = get_category_id($self);
	
	foreach my $key ( keys %new_keywords ) {
		if( defined $new_keywords{$key} ) {
			$result{$key} = $converter{$new_keywords{$key}};
			$new_keywords{$key} = $category_id{$result{$key}};
		}
	}
	
	$self->add_keywords(%new_keywords);
	
	return %result;
};

sub add_keywords {
	my $self = shift;
	my %args = @_;
	my $dbh = $self->{Database};
	my $sql = <<"EOT";
		INSERT INTO keywords ( name, category_id ) VALUES (?,?);
EOT
	$dbh->do('BEGIN');
	my $sth = $dbh->prepare($sql);
	foreach my $key ( keys %args ) {
		if ( defined $args{$key} ) {
			$sth->execute($key,$args{$key});
		}
	}
	$dbh->do('COMMIT');
};

sub get_category_id {
	my $self = shift;
	my %result;
	my $dbh = $self->{Database};
	
	my $sql_query = <<"EOT";
	SELECT categories.category, categories.category_id FROM categories;
EOT
	my $sth = $dbh->prepare($sql_query);
	$sth->execute();
	
	while ( my ( $category, $category_id ) = $sth->fetchrow_array() ) {
		$result{$category} = $category_id;
	}
	
	return %result;
};

1;